 package cn.hd01.service.impl;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.domain.Specifications;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import cn.hd01.common.BaseConstant;
import cn.hd01.common.BaseConstant.CustomerType;
import cn.hd01.repository.CustomerRepository;
import cn.hd01.repository.entity.ConsumeReport;
import cn.hd01.repository.entity.Customer;
import cn.hd01.repository.entity.DistributorDetails;
import cn.hd01.repository.entity.Integration;
import cn.hd01.repository.entity.MyCustomer;
import cn.hd01.repository.entity.StarReport;
import cn.hd01.service.CustomerService;

@Service
public class CustomerServiceImpl extends BaseServiceImpl<Customer, Integer> implements CustomerService {

	private CustomerRepository repository;
	
	@Autowired
	private EntityManager em;
	
	private static final SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd");

	@Autowired
	public CustomerServiceImpl(CustomerRepository repository) {
		super(repository);
		this.repository = repository;
	}

	@Override
	public Page<Customer> findAll(final Customer customer, Pageable pageable) {
		if (customer == null) {
			return repository.findAll(pageable);
		}

		return repository.findAll(Specifications.where(new Specification<Customer>() {
			@Override
			public Predicate toPredicate(Root<Customer> r, CriteriaQuery<?> q, CriteriaBuilder cb) {
				List<Predicate> predicates = new ArrayList<Predicate>();
				
				if (customer.getServantId() != null) {
					predicates.add(cb.equal(r.get("servantId").as(Integer.class), customer.getServantId()));
				}
				
				if (customer.getRecommendDistributorId() != null) {
					predicates.add(cb.equal(r.get("recommendDistributorId").as(String.class), customer.getRecommendDistributorId()));
				}
				
				if (StringUtils.hasText(customer.getLevel())) {
					predicates.add(cb.equal(r.get("level").as(String.class), customer.getLevel()));
				}
				
				if (StringUtils.hasText(customer.getType())) {
					predicates.add(cb.equal(r.get("type").as(String.class), customer.getType()));
				}

				if (StringUtils.hasText(customer.getName())) {
					predicates.add(cb.like(r.get("name").as(String.class), "%" + customer.getName() + "%"));
				}

				if (StringUtils.hasText(customer.getPhoneNo())) {
					predicates.add(cb.like(r.get("phoneNo").as(String.class), "%" + customer.getPhoneNo() + "%"));
				}
				
				if (StringUtils.hasText(customer.getAuditStatus())) {
					predicates.add(cb.equal(r.get("auditStatus").as(String.class), customer.getAuditStatus()));
				}
				
				if(!CollectionUtils.isEmpty(customer.getDistributorIdList())){
					predicates.add(r.get("recommendDistributorId").in(customer.getDistributorIdList()));
				}
				
				if (StringUtils.hasText(customer.getBirthdayBegin())) {
					predicates.add(cb.greaterThanOrEqualTo(r.get("birthdayMmdd").as(String.class), customer.getBirthdayBegin()));
				}
				
				if (StringUtils.hasText(customer.getBirthdayEnd())) {
					predicates.add(cb.lessThanOrEqualTo(r.get("birthdayMmdd").as(String.class), customer.getBirthdayEnd()));
				}
				
				return cb.and(predicates.toArray(new Predicate[] {}));
			}
		}), pageable);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Page<Customer> findDAll(Customer customer, Pageable pageable) {
		String sql = "select c.* from customer c, distributor_details d where c.type = 1 and c.id = d.distributor_id";

		if (StringUtils.hasText(customer.getName())) {
			sql += " and (c.name like '%"+customer.getName()+"%' or d.shop_name like '%"+customer.getName()+"%')";
		}
		
		if (StringUtils.hasText(customer.getPhoneNo())) {
			sql += " and phone_no like '%"+customer.getPhoneNo()+"%'";
		}

		Query query = em.createNativeQuery(sql, Customer.class);
		query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
		query.setMaxResults(pageable.getPageSize());
		List<Customer> objectList = query.getResultList();

		return new PageImpl<Customer>(objectList, pageable, total(customer));
	}

	private long total(Customer customer) {
		String sql = "select count(1) from (select c.* from customer c, distributor_details d where c.type = 1 and c.id = d.distributor_id ";
		
		if (StringUtils.hasText(customer.getName())) {
			sql += " and (c.name like '%"+customer.getName()+"%' or d.shop_name like '%"+customer.getName()+"%')";
		}
		
		if (StringUtils.hasText(customer.getPhoneNo())) {
			sql += " and phone_no like '%"+customer.getPhoneNo()+"%'";
		}

		sql += ") t";
		Query query = em.createNativeQuery(sql);
		return new Long(query.getSingleResult().toString()).longValue();
	}
	
	@Override
	public int updateCustomer(Customer customer) {
		return repository.updateCustomer(customer.getServantId(), customer.getLevelId(), customer.getName(),
				customer.getAddress(), customer.getProvinceId(),
				customer.getProvince(), customer.getCityId(), customer.getCity(), customer.getAreaId(),
				customer.getArea(), customer.getRecommendDistributorId(), customer.getId());
	}

	@Override
	public int updateCustomerStatus(Integer id, String status) {
		return repository.updateCustomerStatus(status, id);
	}

	@Override
	public Customer findByPhoneNo(String phoneNo) {
		return repository.findByPhoneNo(phoneNo);
	}

	@Override
	public Customer findByOauthId(Integer oauthId) {
		return repository.findByOauthId(oauthId);
	}

	@Override
	public int updateCustomerAuditStatus(Integer id, String status) {
		return repository.updateCustomerAuditStatus(status, id);
	}

	@Override
	public int updateCustomerServant(Integer servantId, String type) {
		return repository.updateCustomerServant(servantId, type);
	}

	@Override
	public int updateCustomerDistributor(Integer distributorId) {
		return repository.updateCustomerDistributor(distributorId);
	}
	
	@Override
	public int updateCustomerLevel(Integer levelId) {
		return repository.updateCustomerLevel(levelId);
	}

	@Override
	public Page<Customer> findRecommendCustomer(Integer recommendDistributorId, Pageable pageable) {
		return repository.findByRecommendDistributorIdAndType(recommendDistributorId, CustomerType.CUSTOMER, pageable);
	}

	@Override
	public List<Integer> findDistributorIdByServantId(Integer servantId) {
		return repository.findDistributorIdByServantId(servantId, BaseConstant.CustomerType.DISTRIBUTOR);
	}

	@Override
	public int countByTypeAndAuditStatus(String type, String auditStatus) {
		return repository.countByTypeAndAuditStatus(type, auditStatus);
	}

	@Override
	public int countByTypeAndAuditStatusAndServantId(String type,
			String auditStatus, Integer servantId) {
		return repository.countByTypeAndAuditStatusAndServantId(type, auditStatus, servantId);
	}

	private long total(ConsumeReport report) {
		StringBuffer sql = new StringBuffer("select count(1) from (SELECT 1 FROM c_order o where 1 = 1 ");
		
		if(report.getCreateBeginTime() != null){
			sql.append(" and o.create_time >= '").append(SDF.format(report.getCreateBeginTime())).append("'");
		}
		
		if(report.getCreateEndTime() != null){
			sql.append(" and o.create_time <= '").append(SDF.format(report.getCreateEndTime())).append("'");
		}
		
		sql.append(" group by o.customer_id, o.distributor_id) temp");
		Query query = em.createNativeQuery(sql.toString());
		return new Long(query.getSingleResult().toString()).longValue();
	}
	
	@Override
	public Page<ConsumeReport> findConsumeReport(ConsumeReport report, Pageable pageable) {
		StringBuffer sql =  new StringBuffer("SELECT o.customer_id, o.distributor_id,  o.customer_name, SUM(o.amount) amount, CONCAT(d.`shop_name`, '(', c.`name`, ')') distributor_all_name FROM c_order o LEFT JOIN customer c ON c.id = o.`distributor_id`  LEFT JOIN `distributor_details` d ON c.id = d.`distributor_id` WHERE 1 = 1 ");
		
		if(report.getCreateBeginTime() != null){
			sql.append(" and o.create_time >= '").append(SDF.format(report.getCreateBeginTime())).append("'");
		}
		
		if(report.getCreateEndTime() != null){
			sql.append(" and o.create_time <= '").append(SDF.format(report.getCreateEndTime())).append("'");
		}
		
		sql.append(" GROUP BY o.customer_id, o.customer_name, o.distributor_id, c.`name`, d.`shop_name` order by SUM(o.amount) desc");

		Query query = em.createNativeQuery(sql.toString(), ConsumeReport.class);
		query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
		query.setMaxResults(pageable.getPageSize());
		return new PageImpl<ConsumeReport>(query.getResultList(), pageable, total(report));
	}
	
	@Override
	public List<Customer> findCustomerList(String type) {
		return repository.findCustomerList(type);
	}

	@Override
	public List<Customer> findCustomerList(String type, String auditStatus) {
		return repository.findCustomerList(type,auditStatus);
	}

	@Override
	public List<Customer> findCustomerList(String type, int servantId) {
		return repository.findCustomerList(type,servantId);
	}

	@Override
	public Integer countCustomer(Integer distributorId) {
		return repository.countCustomer(distributorId);
	}

	@Override
	public List<StarReport> findLevelCount() {
		List<Object[]> list = repository.findLevelCount();
		List<StarReport> resultList = new ArrayList<StarReport>();
		if(!CollectionUtils.isEmpty(list)){
			for(Object[] obj : list){
				String starName = String.valueOf(obj[0]);
				Integer count = ((Long) obj[1]).intValue();
				StarReport sp = new StarReport(starName, count);
				resultList.add(sp);
			}
		}
		return resultList;
	}

	@Override
	public List<Object[]> findAllCustomer() {
		return repository.getAllCustomer();
	}

	@Override
	public Page<MyCustomer> findMyCustomer(Integer recommendDistributorId,
			Pageable pageable) {
		StringBuffer sql =  new StringBuffer("SELECT c.`name`, IFNULL(SUM(o.`amount`), 0) amount FROM customer c LEFT JOIN c_order o ON c.id = o.customer_id WHERE TYPE = 0 AND  ");
		
		if(recommendDistributorId != null){
			sql.append("c.`recommend_distributor_id` =").append(recommendDistributorId);
		}
		sql.append(" GROUP BY c.id");

		Query query = em.createNativeQuery(sql.toString(), MyCustomer.class);
		query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
		query.setMaxResults(pageable.getPageSize());
		return new PageImpl<MyCustomer>(query.getResultList(), pageable, myCustomer(recommendDistributorId));
	}
	
	private Long myCustomer(Integer recommendDistributorId){
		StringBuffer sql = new StringBuffer("SELECT count(1) FROM customer c WHERE TYPE = 0 AND   ");
		
		if(recommendDistributorId != null){
			sql.append("c.`recommend_distributor_id` =").append(recommendDistributorId);
		}
		
		Query query = em.createNativeQuery(sql.toString());
		return new Long(query.getSingleResult().toString()).longValue();
	}

}
